package com.plsjavaweb.dao.implementation;

import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import sun.util.logging.resources.logging;

import com.plsjavaweb.dao.DAOFactory;
import com.plsjavaweb.dao.interfaces.ReportsDAO;
import com.plsjavaweb.dao.mapper.CSSeatAllocationMapper;
import com.plsjavaweb.dao.mapper.ProjectSeatAllocationMapper;
import com.plsjavaweb.dao.utilities.DAO;
import com.plsjavaweb.logging.LoggingConstants;
import com.plsjavaweb.pojo.ProjectPOJO;
import com.plsjavaweb.pojo.SeatAllocationPOJO;

public class ReportsDAOImpl implements ReportsDAO {

	static Logger log = Logger.getLogger(DAOFactory.class);
	
	@Override
	public List<ProjectPOJO> getProjectReportsByProjectManager(int managerId) {
		log.info(LoggingConstants.entryMessage);
		List<ProjectPOJO> projectList = new ArrayList<ProjectPOJO>();
		String query = "SELECT  DISTINCT P.project_id,P.project_code,P.project_desc,COUNT(R.employee_id) AS total_resource FROM Project P LEFT JOIN seat S ON P.project_id = S.Project_id LEFT JOIN resource R   ON P.Project_id = R.project_id WHERE P.project_id IN ( SELECT  DISTINCT P2.project_id FROM project P2 INNER JOIN resource R2 ON P2.project_id = r2.project_id INNER JOIN employee_account EA ON EA.employee_id =  R2.employee_id WHERE EA.role='MANAGER' AND EA.employee_id= "+managerId+") AND S.seat_type_id = 40001 GROUP BY P.project_id,P.project_code,P.project_desc,R.employee_id;";
		log.info(LoggingConstants.queryStringCreated);
		
		DAO<ProjectPOJO> dao = new DAO<>();
		ProjectSeatAllocationMapper reportMapper = new ProjectSeatAllocationMapper();
		try{
			log.info(LoggingConstants.tryMessage);
			projectList = dao.ExecuteQuery(query, reportMapper);
		}catch (Exception e){
			log.info(LoggingConstants.catchMessage);
			log.error(e.getMessage());
		} finally {
			log.info(LoggingConstants.returnedMessage);
			return projectList;
		}
	}
	
	@Override
	public List<ProjectPOJO> getProjectReports() {
		log.info(LoggingConstants.entryMessage);
		List<ProjectPOJO> projectList = new ArrayList<ProjectPOJO>();;
		String query = "SELECT  DISTINCT P.project_id,P.project_code,P.project_desc,COUNT(R.employee_id) AS total_resource FROM Project P LEFT JOIN seat S ON P.project_id = S.Project_id LEFT JOIN resource R   ON P.Project_id = R.project_id WHERE P.project_id IN ( SELECT  DISTINCT P2.project_id FROM project P2 INNER JOIN resource R2 ON P2.project_id = r2.project_id INNER JOIN employee_account EA ON EA.employee_id =  R2.employee_id) AND S.seat_type_id = 40001 GROUP BY P.project_id,P.project_code,P.project_desc,R.employee_id;";
		log.info(LoggingConstants.queryStringCreated);
		
		DAO<ProjectPOJO> dao = new DAO<>();
		ProjectSeatAllocationMapper reportMapper = new ProjectSeatAllocationMapper();
		try{
			log.info(LoggingConstants.tryMessage);
			projectList = dao.ExecuteQuery(query, reportMapper);
		}catch (Exception e){
			log.info(LoggingConstants.catchMessage);
			log.error(e.getMessage());
		} finally {
			log.info(LoggingConstants.returnedMessage);
			return projectList;
		}
	}

	@Override
	public SeatAllocationPOJO getSeatAllocationForEmployeePerFloor(String floor) {
		log.info(LoggingConstants.entryMessage);
		SeatAllocationPOJO reports = null;
		String empQuery = "Select B.seat_type_code, Count(*) as 'seat_count' FROM seat A JOIN seat_type B on A.seat_type_id = B.seat_type_id JOIN location_detail C on A.location_detail_id = C.location_detail_id WHERE A.seat_code not like 'PM%' AND C.floor_level = '"+floor+"' Group by B.seat_type_code;";
		log.info(LoggingConstants.queryStringCreated);

		DAO<SeatAllocationPOJO> dao = new DAO<>();
		CSSeatAllocationMapper reportMapper = new CSSeatAllocationMapper();

		try {
			log.info(LoggingConstants.tryMessage);
			reports = dao.ExecuteQuerySingle(empQuery, reportMapper);
		} catch (Exception e) {
			log.info(LoggingConstants.catchMessage);
			log.error(e.getMessage());
			reports = null;
		} finally {
			log.info(LoggingConstants.returnedMessage);
			return reports;
		}
	}

	@Override
	public SeatAllocationPOJO getSeatAllocationForPMPerFloor(String floor) {
		log.info(LoggingConstants.entryMessage);
		SeatAllocationPOJO reports = null;
		String pmQuery = "Select B.seat_type_code, Count(*) as 'seat_count' FROM seat A JOIN seat_type B on A.seat_type_id = B.seat_type_id JOIN location_detail C on A.location_detail_id = C.location_detail_id WHERE A.seat_code like 'PM%' AND C.floor_level = '"+floor+"' Group by B.seat_type_code;";
		log.info(LoggingConstants.queryStringCreated);

		DAO<SeatAllocationPOJO> dao = new DAO<>();
		CSSeatAllocationMapper reportMapper = new CSSeatAllocationMapper();

		try {
			log.info(LoggingConstants.tryMessage);
			reports = dao.ExecuteQuerySingle(pmQuery, reportMapper);
		} catch (Exception e) {
			log.info(LoggingConstants.catchMessage);
			log.error(e.getMessage());
			reports = null;
		} finally {
			log.info(LoggingConstants.returnedMessage);
			return reports;
		}
	}
	
	@Override
	public SeatAllocationPOJO getSeatAllocationAllEmployee() {
		log.info(LoggingConstants.entryMessage);
		SeatAllocationPOJO reports = null;
		String empQuery = "Select B.seat_type_code, Count(*) as 'seat_count' FROM seat A JOIN seat_type B on A.seat_type_id = B.seat_type_id JOIN location_detail C on A.location_detail_id = C.location_detail_id WHERE A.seat_code not like 'PM%' Group by B.seat_type_code;";
		log.info(LoggingConstants.queryStringCreated);

		DAO<SeatAllocationPOJO> dao = new DAO<>();
		CSSeatAllocationMapper reportMapper = new CSSeatAllocationMapper();

		try {
			log.info(LoggingConstants.tryMessage);
			reports = dao.ExecuteQuerySingle(empQuery, reportMapper);
		} catch (Exception e) {
			log.info(LoggingConstants.catchMessage);
			log.error(e.getMessage());
			reports = null;
		} finally {
			log.info(LoggingConstants.returnedMessage);
			return reports;
		}
	}
	
	@Override
	public SeatAllocationPOJO getSeatAllocationAllPM() {
		log.info(LoggingConstants.entryMessage);
		SeatAllocationPOJO reports = null;
		String pmQuery = "Select B.seat_type_code, Count(*) as 'seat_count' FROM seat A JOIN seat_type B on A.seat_type_id = B.seat_type_id JOIN location_detail C on A.location_detail_id = C.location_detail_id WHERE A.seat_code like 'PM%' Group by B.seat_type_code;";
		log.info(LoggingConstants.queryStringCreated);

		DAO<SeatAllocationPOJO> dao = new DAO<>();
		CSSeatAllocationMapper reportMapper = new CSSeatAllocationMapper();

		try {
			log.info(LoggingConstants.tryMessage);
			reports = dao.ExecuteQuerySingle(pmQuery, reportMapper);
		} catch (Exception e) {
			log.info(LoggingConstants.catchMessage);
			log.error(e.getMessage());
			reports = null;
		} finally {
			log.info(LoggingConstants.returnedMessage);
			return reports;
		}
	}
	
	@Override
	public SeatAllocationPOJO getSeatAllocationAll() {
		log.info(LoggingConstants.entryMessage);
		SeatAllocationPOJO reports = null;
		String pmQuery = "Select B.seat_type_code, Count(*) as 'seat_count' FROM seat A JOIN seat_type B on A.seat_type_id = B.seat_type_id JOIN location_detail C on A.location_detail_id = C.location_detail_id Group by B.seat_type_code;";
		log.info(LoggingConstants.queryStringCreated);

		DAO<SeatAllocationPOJO> dao = new DAO<>();
		CSSeatAllocationMapper reportMapper = new CSSeatAllocationMapper();

		try {
			log.info(LoggingConstants.tryMessage);
			reports = dao.ExecuteQuerySingle(pmQuery, reportMapper);
		} catch (Exception e) {
			log.info(LoggingConstants.catchMessage);
			log.error(e.getMessage());
			reports = null;
		} finally {
			log.info(LoggingConstants.returnedMessage);
			return reports;
		}
	}
	
	@Override
	public SeatAllocationPOJO getSeatAllocationAllPerProject(int projectId) {
		log.info(LoggingConstants.entryMessage);
		SeatAllocationPOJO reports = null;
		String pmQuery = "Select B.seat_type_code, Count(*) as 'seat_count' FROM seat A JOIN seat_type B on A.seat_type_id = B.seat_type_id JOIN location_detail C on A.location_detail_id = C.location_detail_id JOIN project D on A.project_id = D.project_id WHERE D.project_id = "+projectId+" Group by B.seat_type_code;";
		log.info(LoggingConstants.queryStringCreated);

		DAO<SeatAllocationPOJO> dao = new DAO<>();
		CSSeatAllocationMapper reportMapper = new CSSeatAllocationMapper();

		try {
			log.info(LoggingConstants.tryMessage);
			reports = dao.ExecuteQuerySingle(pmQuery, reportMapper);
		} catch (Exception e) {
			log.info(LoggingConstants.catchMessage);
			log.error(e.getMessage());
			reports = null;
		} finally {
			log.info(LoggingConstants.returnedMessage);
			return reports;
		}
	}
}
